﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class GroupActivityDAL
    {
        private const string Waming = "SqlServerDAL.GroupActivityDAL";

        public int Save(GroupActivityDto dto)
        {
            try
            {
                var returnValue = 0;
                string sql;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@GaTitle", SqlDbType.Int){Value = dto.GaTitle},
                                         new SqlParameter("@GaType", SqlDbType.Int){Value = dto.GaType},
                                         new SqlParameter("@GaMode", SqlDbType.Int){Value = dto.GaMode},
                                         new SqlParameter("@GaSportId", SqlDbType.Int){Value = dto.GaSportId},
                                         new SqlParameter("@GaStartDate", SqlDbType.DateTime){Value = dto.GaStartDate},
                                         new SqlParameter("@GaEndDate", SqlDbType.DateTime){Value = dto.GaEndDate},
                                         new SqlParameter("@GaStartTime", SqlDbType.VarChar){Value = dto.GaStartTime},
                                         new SqlParameter("@GaEndTime", SqlDbType.VarChar){Value = dto.GaEndTime},
                                         new SqlParameter("@GaCity", SqlDbType.VarChar){Value = dto.GaCity},
                                         new SqlParameter("@GaAddress", SqlDbType.VarChar){Value = dto.GaAddress},
                                         new SqlParameter("@GaPayType", SqlDbType.Int){Value = dto.GaPayType},
                                         new SqlParameter("@GaFee", SqlDbType.Decimal){Value = dto.GaFee},
                                         new SqlParameter("@GaRemark", SqlDbType.VarChar){Value = dto.GaRemark},
                                         new SqlParameter("@GaInputTime", SqlDbType.DateTime){Value = dto.GaInputTime},
                                         new SqlParameter("@GaGroupId", SqlDbType.Int){Value = dto.GaGroupId},
                                         new SqlParameter("@GaSponsor", SqlDbType.Int){Value = dto.GaSponsor},
                                         new SqlParameter("@GaCurState", SqlDbType.Int){Value = dto.GaCurState},
                                         new SqlParameter("@GaAudit", SqlDbType.Int){Value = dto.GaAudit},
                                         new SqlParameter("@GaFrontCover", SqlDbType.Int){Value = dto.GaFrontCover},
                                     };

                if (dto.GaId == 0)
                {
                    sql =
                        @"insert into GroupActivity(GaTitle,GaType,GaMode,GaSportId,GaStartDate,GaEndDate,GaStartTime,GaEndTime,GaCity,GaAddress,
                          GaPayType,GaFee,GaRemark,GaInputTime,GaGroupId,GaSponsor,GaCurState,GaAudit,GaFrontCover) values(@GaTitle,@GaType,@GaMode,
                          @GaSportId,@GaStartDate,@GaEndDate,@GaStartTime,@GaEndTime,@GaCity,@GaAddress,@GaPayType,@GaFee,@GaRemark,@GaInputTime,
                          @GaGroupId,@GaSponsor,@GaCurState,@GaAudit,@GaFrontCover) select @@identity as resultId";
                    returnValue = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()));
                }
                else
                {
                    sql =
                        @"update GroupActivity set GaTitle=@GaTitle,GaType=@GaType,GaMode=@GaMode,GaSportId=@GaSportId,GaStartDate=@GaStartDate,
                          GaEndDate=@GaEndDate,GaStartTime=@GaStartTime,GaEndTime=@GaEndTime,GaCity=@GaCity,GaAddress=@GaAddress,GaPayType=@GaPayType,
                          GaFee=@GaFee,GaRemark=@GaRemark,GaInputTime=@GaInputTime,GaGroupId=@GaGroupId,GaSponsor=@GaSponsor,GaCurState=@GaCurState,
                          GaAudit=@GaAudit,GaFrontCover=@GaFrontCover where GaId=@GaId";
                    parameters.Add(new SqlParameter("@GaId", SqlDbType.Int) { Value = dto.GaId });
                    returnValue = SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                }
                return returnValue;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".Save(GroupActivityDto dto)", ex);
                return -1;
            }
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, int groupId, int channelId, string order)
        {
            try
            {
                var hts = new List<Hashtable>();
                order = string.IsNullOrEmpty(order) ? "order by GaInputTime desc" : order;
                var sql = @"select row_number() over (" + order + ") as rowNumber,* from GroupActivity ";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (groupId != 0)
                {
                    whereSql = " where GaGroupId=@GaGroupId";
                    parameters.Add(new SqlParameter("@GaGroupId", SqlDbType.Int) { Value = groupId });
                }
                if (channelId != 0)
                {
                    whereSql += channelId != 0
                                    ? " where GaGroupId in (select GroupId from SportsGroup where GroupChannel like @GroupChannel)"
                                    : " and GaGroupId in (select GroupId from SportsGroup where GroupChannel like @GroupChannel)";
                    parameters.Add(new SqlParameter("@GroupChannel", SqlDbType.Int) { Value = "%," + channelId + ",%" });
                }
                sql = string.Format(
                    "select * from ({0} {1}) as GroupActivity where rowNumber between {2} AND {3}",
                    sql, whereSql, firstResult, maxResult);
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    while (red.Read())
                    {
                        hts.Add(SqlHelper.FillHashtable(red));
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SlicedFindAll(int firstResult, int maxResult, int groupId, string order)", ex);
                return null;
            }
        }

        public int SelectCount(int groupId, int channelId)
        {
            try
            {
                const string sql = "select count(*) from GroupActivity";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (groupId != 0)
                {
                    whereSql = " where GaGroupId=@GaGroupId";
                    parameters.Add(new SqlParameter("@GaGroupId", SqlDbType.Int) { Value = groupId });
                }
                if (channelId != 0)
                {
                    whereSql += channelId != 0
                                    ? " where GaGroupId in (select GroupId from SportsGroup where GroupChannel like @GroupChannel)"
                                    : " and GaGroupId in (select GroupId from SportsGroup where GroupChannel like @GroupChannel)";
                    parameters.Add(new SqlParameter("@GroupChannel", SqlDbType.Int) { Value = "%," + channelId + ",%" });
                }
                var red = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql + whereSql,
                                                  parameters.ToArray());
                return red == DBNull.Value ? 0 : Convert.ToInt32(red);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(Waming + ".SelectCount(int groupId)", ex);
                return -1;
            }
        }
    }
}
